"""
查询数据
    sqlalachemy 的查询语法较多, 接下来通过一个案例来进行综合演练
    案例说明
    案例中包含一个模型类 User, 对应 users表, 包含四个字段: id(主键), name, email, age
    首先运行案例代码, 生成测试数据
        程序启动后会重置 users表, 并向其中添加10条用户数据
    为了方便展示查询结果, 建议使用 交互模式 测试查询语句
        推荐使用 ipython包, 相比 python自带的交互模式 有语法提示
        安装包 pip install ipython
    关于输出结果
        内置方法__repr__() 是 __str__()方法 的升级版, 可以修改 print(对象) 和 交互模式下对象 的输出结果
        案例中将 模型对象的输出结果 修改为 输出模型对象的所有属性值 (记录的数据), 以便验证查询结果

查询语法
    两套查询语法:
        1. 查询结果 = 模型类.query[.查询过滤器].查询执行器
            返回的查询结果中数据单元为对应的 模型对象
        2. 组件对象.session.query(字段)[.查询过滤器].查询执行器
            返回的数据单元为 类元组对象, 该类型支持 索引、属性名 以及 别名 三种取值方式

常用的SQLAlchemy查询执行器:
    执行器的特点:
        将整个查询语句转换为SQL语句并 执行查询
        在查询语句的末尾设置, 每条查询语句 只能设置一个执行器
    方法	                            说明
    all()	                返回列表, 元素为所有符合查询的模型对象
    count()	                返回查询结果的数量
    first()	                返回符合查询的第一个模型对象，如果未查到，返回None
    first_or_404()	        返回符合查询的第一个模型对象，如果未查到，返回404
    get(主键)	            返回主键对应的模型对象，如不存在，返回None
    get_or_404(主键)	        返回指定主键对应的模型对象，如不存在，返回404
    paginate(页码, 每页条数)	返回一个Paginate对象，它包含分页查询的结果


常用的SQLAlchemy查询过滤器:
    过滤器的特点:
        只负责设置过滤条件, 不会执行查询(查询由执行器来完成)
        允许续接其他过滤器 或 执行器
    过滤器	                        说明
    filter_by(字段名=值)	        把等值过滤器添加到原查询上，返回BaseQuery对象
    filter(函数引用/比较运算)	    把过滤器添加到原查询上，返回BaseQuery对象
    limit(限定条数)	            使用指定的值限定原查询返回的结果，返回BaseQuery对象
    offset(偏移条数)	            根据指定的值按照原查询进行偏移查询，返回BaseQuery对象
    order_by(排序字段)	        根据指定条件对原查询结果进行排序，返回BaseQuery对象
    group_by(分组字段)	        根据指定条件对原查询结果进行分组，返回BaseQuery对象
    options()	                针对原查询限定查询的字段，返回BaseQuery对象

注意点:
    查询过滤器返回的都是 BaseQuery类型对象, 该对象支持链式调用, 即可以续接其他过滤器 或 执行器
    如果考虑到性能优化, 应该避免 select *, 只查询需求字段 (select * 会导致数据库服务器去获取&解析&处理目标数据的每个字段, 对服务器资源造成浪费, 并且不必要的数据也会占用更多的 网络IO 和 数据库查询缓存)
"""
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# 相关配置
# 设置数据库连接地址
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql://root:mysql@127.0.0.1:3306/Test"
# # 是否追踪数据库修改(开启后会触发一些钩子函数)  一般不开启, 会影响性能
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 是否显示底层执行的SQL语句
app.config['SQLALCHEMY_ECHO'] = True

# 初始化组件对象, 直接关联Flask应用
db = SQLAlchemy(app)


# 构建模型类  类->表  类属性->字段  实例对象->记录
class User(db.Model):
    """
    注意点:
        模型类必须继承 db.Model, 其中 db 指对应的组件对象
        表名默认为类名小写, 可以通过 __tablename__类属性 进行修改
        类属性对应字段, 必须是通过 db.Column() 创建的对象
        可以通过 create_all() 和 drop_all()方法 来创建和删除所有模型类对应的表
    """
    __tablename__ = "users"  # 表名 默认使用类名的小写
    # 定义类属性 记录字段
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    email = db.Column(db.String(64))
    age = db.Column(db.Integer)

    def __repr__(self):  # 自定义 交互模式 & print() 的对象打印
        return "(%s, %s, %s, %s)" % (self.id, self.name, self.email, self.age)


@app.route('/')
def index():
    """查询数据

    # 查询所有用户数据
    User.query.all() 返回列表, 元素为模型对象

    # 查询有多少个用户
    User.query.count()

    # 查询第1个用户
    User.query.first()  返回模型对象/None

    # 查询id为4的用户[3种方式]
    # 方式1: 根据id查询  返回模型对象/None
    User.query.get(4)

    # 方式2: 等值过滤器 关键字实参设置字段值  返回BaseQuery对象
    # BaseQuery对象可以续接其他过滤器/执行器  如 all/count/first等
    User.query.filter_by(id=4).all()

    # 方式3: 复杂过滤器  参数为比较运算/函数引用等  返回BaseQuery对象
    User.query.filter(User.id == 4).first()

    # 查询名字结尾字符为g的所有用户[开始 / 包含]
    User.query.filter(User.name.endswith("g")).all()
    User.query.filter(User.name.startswith("w")).all()
    User.query.filter(User.name.contains("n")).all()
    User.query.filter(User.name.like("w%n%g")).all()  # 模糊查询

    # 查询名字和邮箱都以li开头的所有用户[2种方式]
    User.query.filter(User.name.startswith('li'), User.email.startswith('li')).all()
    from sqlalchemy import and_
    User.query.filter(and_(User.name.startswith('li'), User.email.startswith('li'))).all()

    # 查询age是25 或者 `email`以`itheima.com`结尾的所有用户
    from sqlalchemy import or_
    User.query.filter(or_(User.age==25, User.email.endswith("itheima.com"))).all()

    # 查询名字不等于wang的所有用户[2种方式]
    from sqlalchemy import not_
    User.query.filter(not_(User.name == 'wang')).all()
    User.query.filter(User.name != 'wang').all()

    # 查询id为[1, 3, 5, 7, 9]的用户
    User.query.filter(User.id.in_([1, 3, 5, 7, 9])).all()

    # 所有用户先按年龄从小到大, 再按id从大到小排序, 取前5个
    User.query.order_by(User.age, User.id.desc()).limit(5).all()

    # 查询年龄从小到大第2-5位的数据   2 3 4 5
    User.query.order_by(User.age).offset(1).limit(4).all()

    # 分页查询, 每页3个, 查询第2页的数据  paginate(页码, 每页条数)
    pn = User.query.paginate(2, 3)
    pn.pages 总页数  pn.page 当前页码 pn.items 当前页的数据  pn.total 总条数

    # 查询每个年龄的人数    select age, count(name) from t_user group by age  分组聚合
    from sqlalchemy import func
    data = db.session.query(User.age, func.count(User.id).label("count")).group_by(User.age).all()
    for item in data:
        # print(item[0], item[1])
        print(item.age, item.count)  # 建议通过label()方法给字段起别名, 以属性方式获取数据


    # 只查询所有人的姓名和邮箱  优化查询   User.query.all()  # 相当于select *
    from sqlalchemy.orm import load_only
    data = User.query.options(load_only(User.name, User.email)).all()  # flask-sqlalchem的语法
    for item in data:
        print(item.name, item.email)

    data = db.session.query(User.name, User.email).all()  # sqlalchemy本体的语法
    for item in data:
        print(item.name, item.email)
    """

    return "index"


if __name__ == '__main__':
    # 删除所有继承自db.Model的表
    db.drop_all()
    # 创建所有继承自db.Model的表
    db.create_all()

    # 添加测试数据
    user1 = User(name='wang', email='wang@163.com', age=20)
    user2 = User(name='zhang', email='zhang@189.com', age=33)
    user3 = User(name='chen', email='chen@126.com', age=23)
    user4 = User(name='zhou', email='zhou@163.com', age=29)
    user5 = User(name='tang', email='tang@itheima.com', age=25)
    user6 = User(name='wu', email='wu@gmail.com', age=25)
    user7 = User(name='qian', email='qian@gmail.com', age=23)
    user8 = User(name='liu', email='liu@itheima.com', age=30)
    user9 = User(name='li', email='li@163.com', age=28)
    user10 = User(name='sun', email='sun@163.com', age=26)

    # 一次添加多条数据
    db.session.add_all([user1, user2, user3, user4, user5, user6, user7, user8, user9, user10])
    db.session.commit()

    app.run(debug=True)
